package Main;
import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class Util {
	
	/**
	 * Print the data in ResultSet to Console
	 * @param rs
	 */
	public static void printDBData(ResultSet rs){
		ResultSetMetaData rsmd;
		try {
			rsmd = rs.getMetaData();
		
			int j = 0;
			j = rsmd.getColumnCount();
			
			for(int k = 0; k<j; k++){
				System.out.print(rsmd.getCatalogName(k+1));
				System.out.print("\t");
			}
			
			System.out.println();
			
			while(rs.next()){
				for(int i=0;i<j;i++){
					if(rsmd.getColumnName(i+i).equals("running_time")){
						System.out.print("[" + rs.getTime(i+1) + "]");
						System.out.print("\t");
					}
					System.out.print(rs.getString(i+1));
					System.out.print("\t");
				}
				System.out.println();
			}
			
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * Remove blank in String
	 * @param str
	 * @return
	 */
	public static String replaceBlank(String str) {
        String dest = "";
        if (str!=null) {
            Pattern p = Pattern.compile("\\s*|\t|\r|\n");
            Matcher m = p.matcher(str);
            dest = m.replaceAll("");
        }
        return dest;
    }
	
	/**
	 * Get current time
	 */
	public static String getCurrentTime(){
		Date now = new Date();
		
		DateFormat d2 = DateFormat.getDateTimeInstance(); 
	    String time = d2.format(now); 
		
		return time;
	}
	
	public static int writeToLocalDB_bus_line(ArrayList<String> data){			
		String user = "root";
		String pwd = "root";
		String url = "jdbc:mysql://localhost:3306/bjbus";

		String tableName = "beijing_bus_line";
		String sqlstr;
		
		JDBCConnecter mJDBCConnecter = new JDBCConnecter(url, user, pwd);
		mJDBCConnecter.connectDB();
		
		sqlstr = "insert into " + tableName +
				 "(bus_name,starting_station,ending_station,total_station,starting_bus_time,ending_bus_time,bus_card_price,fare,price,mileage) " + 
				 "values(" + 
				 "'" + data.get(0) + "'" +
				 ",'" + data.get(1) + "'" +
				 ",'" + data.get(2) + "'" +
				 "," + data.get(3) +
				 ",'" + data.get(4) + "'" +
				 ",'" + data.get(5) + "'" +
				 ",'" + data.get(6) + "'" +
				 ",'" + data.get(7) + "'" +
				 ",'" + data.get(8) + "'" +
				 ",'" + data.get(9) + "'" +
				 ");";
		
		//System.out.println(data.get(19));
		int ret = mJDBCConnecter.executeInsert(sqlstr);
		
		mJDBCConnecter.closeDB();
		
		return ret;
	}
	
	public static int writeToLocalDB_train_station(ArrayList<String> data){
		String user = "root";
		String pwd = "root";
		String url = "jdbc:mysql://localhost:3306/localmobile";

		String tableName = "local_train_station";
		String sqlstr;
		
		JDBCConnecter mJDBCConnecter = new JDBCConnecter(url, user, pwd);
		mJDBCConnecter.connectDB();
		
		sqlstr = "insert into " + tableName +
				 "(train_name,number,running_time,station_name,arrive_time,starting_time,distance,hard_seat_price,soft_seat_price,hard_top_berth_price,hard_center_berth_price,hard_bottom_berth_price,soft_top_berth_price,soft_bottom_berth_price,business_seat_price,first_seat_price,second_seat_price) " + 
				 "values(" + 
				 "'" + data.get(0) + "'" +
				 "," + data.get(1) +
				 ",'" + data.get(2) + "'" +
				 ",'" + data.get(3) + "'" +
				 ",'" + data.get(4) + "'" +
				 ",'" + data.get(5) + "'" +
				 "," + data.get(6) +
				 "," + data.get(7) +
				 "," + data.get(8) + 
				 "," + data.get(9) + 
				 "," + data.get(10) + 
				 "," + data.get(11) + 
				 "," + data.get(12) + 
				 "," + data.get(13) +
				 "," + data.get(14) +
				 "," + data.get(15) + 
				 "," + data.get(16) + 
				 ");";
		
		//System.out.println(sqlstr);
		int ret = mJDBCConnecter.executeInsert(sqlstr);
		
		mJDBCConnecter.closeDB();
		
		return ret;
	}
	
	public static int writeToLocalDB_bj_bus_list(ArrayList<String> data){
		String user = "root";
		String pwd = "root";
		String url = "jdbc:mysql://localhost:3306/bjbus";

		String tableName = "beijing_bus_list";
		String sqlstr;
		
		JDBCConnecter mJDBCConnecter = new JDBCConnecter(url, user, pwd);
		mJDBCConnecter.connectDB();
		
		sqlstr = "insert into " + tableName +
				 "(bus_name,url) " + 
				 "values(" + 
				 "'" + data.get(0) + "'" +
				 ",'" + data.get(1) + "'" +
				 ");";
		
		System.out.println(sqlstr);
		int ret = mJDBCConnecter.executeInsert(sqlstr);
		
		mJDBCConnecter.closeDB();
		
		return ret;
	}
	
	public static void testRemoteDB(){
		String user = "root";
		String pwd = "";
		String url = "jdbc:mysql://10.232.31.106:3306/mobile";

		String tableName = "train_line";
		String sqlstr;
		ResultSet rs = null;
		
		JDBCConnecter mJDBCConnecter = new JDBCConnecter(url, user, pwd);
		mJDBCConnecter.connectDB();
				
		sqlstr = "select * from " + tableName + " where name='6438'";
		rs = mJDBCConnecter.executeQuery(sqlstr);
		
		Util.printDBData(rs);
		
		mJDBCConnecter.closeDB();
	}
	
	public static ArrayList<String> readFileByLines(String fileName) {
		ArrayList<String> al = new ArrayList<String>();
				
        File file = new File(fileName);
        BufferedReader reader = null;
        try {
            reader = new BufferedReader(new FileReader(file));
            String tempString = null;

            while ((tempString = reader.readLine()) != null) {
            	al.add(tempString);
            }
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e1) {
                }
            }
        }
        
        return al;
        
	}

	public static String convertByteToString(byte[] b) {
        String str = "";       
		
        for(byte bb : b){
			
	        str += new String(new byte[] {bb});
		}

        return str;
    }
	
	/**
	 * 读取文件，以String形式返回其内容
	 * @param f
	 * @return 
	 * @throws IOException
	 */
	public static String loadAFileToStringDE1(File f){  
	    InputStream is = null;
	    String ret = null;
	    try {
	        is = new BufferedInputStream( new FileInputStream(f) );
	        long contentLength = f.length();
	        ByteArrayOutputStream outstream = new ByteArrayOutputStream( contentLength > 0 ? (int) contentLength : 1024);
	        byte[] buffer = new byte[4096];
	        int len;
	        while ((len = is.read(buffer)) > 0) {
	            outstream.write(buffer, 0, len);
	        }            
	        outstream.close();
	        ret = outstream.toString();
	        //byte[] ba = outstream.toByteArray();
	        //ret = new String(ba);
	    }catch(IOException e){
	    	e.printStackTrace();
	    } finally {
	        if(is!=null) {try{is.close();} catch(Exception e){} }
	    }
	    
	    return ret;        
	}
	 
}
